﻿# 自诊断逻辑,轮询sensor测点表
import json
import numpy as np
import pymysql
import datetime, re
from datetime import timedelta
from itertools import groupby
from utilsapp.common import  get_collection_name

from dateutil.relativedelta import relativedelta
import time
import traceback
from collections import Counter
import itertools
# 连接mysql
def get_base_db(dbname, config):
    try:
        pwd = config['password'] if 'password' in config else config['pwd']
        return pymysql.connect(host=config["host"], port=config["port"], db=dbname,
                               user=config["user"],
                               password=pwd,
                               connect_timeout=20,
                               write_timeout=300,
                               cursorclass=pymysql.cursors.DictCursor)
    except Exception as e:
        print('网络异常，稍后重试', e, config)
        return None


def index(app, sleep_secs):
    """
    主循环函数，用于定期执行测点状态同步。

    参数:
    - app: 应用实例，提供配置和数据库连接等服务。
    - sleep_secs: 循环间隔时间，单位为秒。

    无返回值。
    """
    config = app.config['settings']
    # 检查是否关闭了测点同步功能
    if 'sync_sensor_status' in config['business'] and not config['business']['sync_sensor_status']:
        print('测点同步已关闭', config['business']['sync_sensor_status'])
        return

    while True:
        try:
            # 尝试执行测点状态同步的主逻辑
            print('测点状态同步开始', date_to_str())
            main( config['business'], app)
            print('测点状态同步结束', date_to_str())
        except Exception as e:
            # 打印异常信息，并记录测点同步异常
            traceback.print_exc()
            print('测点状态同步异常',e)
        finally:
            # 间隔一段时间后继续执行循环
            time.sleep(sleep_secs)



def main( business_config, app):

    # 获取数据库连接池
    db_pool = app.db_pool
    if not hasattr(app, 'mgdb') or app.mgdb is None:
        mgdb = False
    else:
        mgdb = app.mgdb
        redis =  app.redis



    # 获取传感器信息并过滤无效传感器
    sensors, warn_ids = get_sensors(db_pool)
    proj_no_dict = get_regionlevel_proj_no(db_pool)
    if not sensors:
        return

    # 当前时间
    now_time = datetime.datetime.now()

    db_base_conn = db_pool.connection()
    b_cur = db_base_conn.cursor(pymysql.cursors.DictCursor)

    sensor_status_dict = {"在线": [], "离线": [], "异常": []}
    icount = 0
    device_sensor_status = {}
    device_sensor_status_zx = {}
    for t in sensors:
        if icount > 6:
            print('传感器状态同步程序异常')
            break
        try:
            t['state'] = '离线'
            t['proj_no'] = 0
            proj_no = proj_no_dict[t['group_id']]
            lib_name = f'ieplat_{proj_no}'
            ieplat_mongodb = mgdb[lib_name]
            mg_addr = t['address'] if '_' in t['address'] else f"{t['address']}_0"
            dbname = get_collection_name(ieplat_mongodb , f"eige_{t['port']}_{mg_addr.split('_')[0]}" , redis)
            collection = ieplat_mongodb[dbname]
            id_stime = int((now_time - timedelta(days=30)).timestamp())
            id_etime = int(now_time.timestamp())
            query_eige = {"$and": [{"_id": {"$gte": f"{t['port']}_{mg_addr}_{id_stime}"}},
                                   {"_id": {"$lte": f"{t['port']}_{mg_addr}_{id_etime}"}}, ]}
            json_con = load_json(t['config'])

            config = json_con[str(t['id'])] if json_con and str(t['id']) in json_con else None

            
            for i in range(2):
                try:
 
 

 
                    projection =  {"_id": 1, "data.Z.vel_rms": 1, "data.X.vel_rms": 1,"data.Y.vel_rms": 1, "data.tem": 1}
                    eiges = list(collection.find(query_eige ,projection).sort("_id",-1).limit(50)) 
                
                    if len(eiges) > 0:
                         
                        tt = datetime.datetime.fromtimestamp(int(eiges[0]['_id'][-10:]))
                        md = eiges[0]['data'].get('Z', None)
                        tem = eiges[0]['data'].get('tem', None)

                        rms = to_float(md.get('vel_rms', None) if md is not None else None )

                        total_tem = []
                        for e in eiges:
                            t_v_tem = to_float(e.get('data', {}).get('tem', None))
                            if t_v_tem is not None:
                                total_tem.append(t_v_tem)

                        count_r = Counter(total_tem).most_common(1)
                        count_r = [0,0] if len(count_r)<=0 else count_r[0]
               
                        yctem = config['yctem'] if config else  [-40, 85]
                        ycvel = config['ycvel'] if config else [0, 20]

                        if (now_time - tt).total_seconds() > 2 * 60 * 60:
                            t['state'] = '离线'
                            break
                        elif (tem is not None and (tem < to_float(yctem[0],-40) or  tem > to_float(yctem[1],85)  or total_tem.count(22) >= 25)) or (
                                rms is not None and (rms < to_float(ycvel[0],0) or rms > to_float(ycvel[1],20))):
                            t['state'] = '异常'
                        else:
                            t['state'] = '在线'

                            if str(t['sensor_type']) != "2": #排除电流
                                device_sensor_status_zx[t['device_id']]=0

                            x_rms = to_float(eiges[0]['data'].get('X', {}).get('vel_rms', None))
                            y_rms = to_float(eiges[0]['data'].get('Y', {}).get('vel_rms', None))
    
                            rms_arr = [x for x in [rms, x_rms, y_rms] if x is not None]

                            qt_rms = None if config is None else to_float(config['qt'],None)
                            if config and qt_rms is None:
                                break

                            qt_rms = qt_rms if qt_rms  else 0.3

                            if t['sensor_type'] != 26 and len(t['direction']) > 0 and len(rms_arr) > 0:
                                max_rms = max(rms_arr)
                                if t['device_id'] in device_sensor_status:
                                    device_sensor_status[t['device_id']].append(max_rms < qt_rms)
                                else:
                                    device_sensor_status[t['device_id']] = [max_rms < qt_rms]
                    
                    break
                except Exception as e:
                    
                    traceback.print_exc()
                    icount += 1


            
            sensor_status_dict[t['state']].append(str(t['id']))
        except Exception as e:
            print('list(group) for异常', e)

    # 打印设备状态检测信息
    print('自主设备状态检测', business_config['sync_device_status'])
    if business_config['sync_device_status'] == True:
        device_ids = {item["device_id"] for item in sensors}
        # 设备状态(0停机、1正常、2告警、3离线)
        d_status = {0: [], 1: [], 2: [], 3: []}
        for d_id in device_ids:
            state = 1 if d_id in device_sensor_status_zx else 3
            if d_id in device_sensor_status:
                state = 0 if len(device_sensor_status[d_id])>0 and all(device_sensor_status[d_id]) else state
            # 如果设备ID在警告列表中，将状态设置为2
            state = state if d_id not in warn_ids else 2
            d_status[state].append(str(d_id))
        sql = f"UPDATE `device` SET `status`=3;"
        iresult = b_cur.execute(sql)
        for state in d_status:
            try:
                ids = d_status[state]
                if len(ids) <= 0:
                    continue
                ids = ','.join(ids)
                sql = f"UPDATE `device` SET `status`={state} WHERE id in ({ids})"
                iresult = b_cur.execute(sql)
                print(f'设备状态:[{state}];{ids}')
            except Exception as e:
                traceback.print_exc()
                print('设备状态更新异常', 'for state in d_status', e)


    iresult = b_cur.execute(f"UPDATE `sensor` SET `status`='离线';")
    for status_key in sensor_status_dict:
        ids = sensor_status_dict[status_key]
        if len(ids)<=0:
            continue
        ids = ','.join(ids)
        iresult = b_cur.execute(f"UPDATE `sensor` SET `status`='{status_key}' WHERE id in ({ids})")
        # print(f'传感器状态更新：[{status_key}];{ids}')

def check_consecutive_groupby(arr,con=25):
    for key, group in itertools.groupby(arr):
        if len(list(group)) >= con:
            return True
    return False


def load_json(strv):
    try:
        if strv is None or len(strv)<=0:
            return None
        return json.loads(strv)
    except:
        return None


def to_float(str_value,def_v=None):
    try:

        if str_value is None:
            return def_v
        elif type(str_value) is float:
            return str_value
        elif len(str(str_value)) <= 0:
            return def_v

        return float(str_value)
    except Exception as e:
        return def_v


def diff_now_time(t_str, seconds):
    if t_str is None or len(t_str) <= 0:
        return False
    # 转换字符串为 datetime 对象
    tt = datetime.strptime(t_str, "%Y-%m-%d %H:%M:%S")
    return (datetime.now() - tt).total_seconds() <= seconds


def get_sensors(pool):
    """
    从数据库获取所有传感器的信息以及需要警告的设备ID列表。

    参数:
    - pool: 数据库连接池对象，用于获取数据库连接。

    返回值:
    - sensors: 包含所有传感器信息的列表，每个元素是包含传感器各种属性的字典。
    - warn_ids: 需要警告的设备ID列表。
    """
    conn = pool.connection()  # 从连接池中获取数据库连接
    cur = conn.cursor(pymysql.cursors.DictCursor)  # 创建游标，使用字典模式返回查询结果
    try:
        # 查询传感器表中所有列的信息
        sql = 'SELECT s.id,s.sensor_name,s.address,s.port,s.device_id,s.direction,s.sensor_type,s.group_id,d.config FROM sensor s left join device d on s.device_id=d.id'
        cur.execute(sql)  # 执行SQL查询
        sensors = cur.fetchall()  # 获取所有查询结果

        # 查询警告信息中未通知且需要显示的设备ID
        cur.execute('SELECT DISTINCT device_id FROM warn where notice=0 and is_show=1')
        warn_ids = cur.fetchall()  # 获取查询结果
        warn_ids = [t['device_id'] for t in warn_ids]  # 将查询结果转换为设备ID列表
        return sensors, warn_ids
    except Exception as e:
        # 异常处理，打印堆栈信息和异常信息
        traceback.print_exc()
        print('get_sensors 异常', e)
        return [], []  # 发生异常时返回空列表
    finally:
        # 清理资源，关闭游标和数据库连接
        cur.close()
        conn.close()


def get_regionlevel_proj_no(pool):
    """
    从数据库获取所有传感器的信息以及需要警告的设备ID列表。

    参数:
    - pool: 数据库连接池对象，用于获取数据库连接。

    返回值:
    - proj_no_dict:
    """
    conn = pool.connection()  # 从连接池中获取数据库连接
    cur = conn.cursor(pymysql.cursors.DictCursor)  # 创建游标，使用字典模式返回查询结果
    try:
        # 查询传感器表中所有列的信息
        sql = "SELECT id,proj_no FROM regionlevel s where type='group'"
        cur.execute(sql)  # 执行SQL查询
        proj_no = cur.fetchall()  # 获取所有查询结果
        proj_no_dict = {}
        for p in proj_no:
            proj_no_dict[p['id']] = p['proj_no']
        return proj_no_dict
    except Exception as e:
        # 异常处理，打印堆栈信息和异常信息
        traceback.print_exc()
        print('get_sensors 异常', e)
        return []  # 发生异常时返回空列表
    finally:
        # 清理资源，关闭游标和数据库连接
        cur.close()
        conn.close()


def date_to_str(fomat='%Y-%m-%d %H:%M:%S'):
    return datetime.datetime.now().strftime(fomat)


def str_to_date(str_date, fomat='%Y-%m-%d %H:%M:%S'):
    return datetime.datetime.strptime(str_date, fomat)
